package cc.ab01;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import oracle.to.mysql.utils.SqlUtils;

/**
 * 备份
 *
 */
public class A2_AB01_JYT_Backup {

	private static String mysqlInsertSql;
	private static int mysqlcolsSql;
	private static Logger log = Logger.getLogger(" ");
	private static String MYSQL_TABLE = "AB01";
	private static String MYSQL_TABLE_JYT_BACKUP = "AB01_JYT_BACKUP";
	private static final String jyt_url = "jdbc:mysql://10.20.0.32:3306/business?user=jlrcbusiness&password=jlrcbusiness&useUnicode=true&useSSL=false&characterEncoding=UTF8";
	private static final String local_url = "jdbc:mysql://localhost:3306/jyt_cc?user=root&password=root&useUnicode=true&useSSL=false&characterEncoding=UTF8";

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			log.error("驱动加载异常：" + e.getMessage());
		}
	}

	public static void main(String[] args) throws SQLException, IOException {
		Connection connMysqlLocal = DriverManager.getConnection(local_url);
		getInsertSql(connMysqlLocal);
		Statement stmt = null;
		ResultSet rs = null;
		Connection connMysqlJYT = null;
		int error = 0;
		int index = 0;
		try {
			stmt = connMysqlLocal.createStatement();
			rs = stmt.executeQuery("select aab001 from ab01_temp");
			StringBuffer sb = new StringBuffer(256);
			while (rs.next()) {
				String aab001 = rs.getString(1);
				sb.append("'" + aab001 + "'");
				sb.append(",");
			}
			rs.close();
			connMysqlJYT = DriverManager.getConnection(jyt_url);
			Statement stmtJYT = connMysqlJYT.createStatement();
			String sql = "select * from ab01 where aab001 in(" + sb.toString().substring(0, sb.length() - 1) + ")";
			ResultSet rsJYT = stmtJYT.executeQuery(sql);
			while (rsJYT.next()) {
				index++;
				PreparedStatement stmtLocal = connMysqlLocal.prepareStatement(mysqlInsertSql);
				for (int c = 1; c <= mysqlcolsSql; c++) {
					Object o = rsJYT.getObject(c);
					if (o != null) {
						if (o instanceof oracle.sql.TIMESTAMP) {
							oracle.sql.TIMESTAMP ts = (oracle.sql.TIMESTAMP) o;
							o = new java.sql.Timestamp(ts.timestampValue().getTime());
						} else if (o instanceof java.util.Date) {
							o = new java.sql.Timestamp(((java.util.Date) o).getTime());
						} else if (o instanceof java.sql.Clob) {
							oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
							Reader red = clob.getCharacterStream();
							char[] crs = new char[10240];
							String tmp = "";
							int len;
							while ((len = red.read(crs)) != -1) {
								tmp += new String(crs, 0, len);
							}
							red.close();
							o = new String(tmp);
						}
					}
					stmtLocal.setObject(c, o);
				}
				try {
					stmtLocal.execute();
					System.out.println(stmtLocal);
				} catch (Exception e) {
					log.error("插入mysql异常,"
							+ e.getMessage().replace("Duplicate entry '", "").replace("' for key 'PRIMARY'", "")
							+ ",sql:" + stmtLocal.toString());
					error++;
				} finally {
					stmtLocal.close();
				}
			}
			stmtJYT.close();
		} catch (Exception e) {
			log.error("异常：" + e.getMessage());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (connMysqlLocal != null) {
					connMysqlLocal.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (connMysqlJYT != null) {
					connMysqlJYT.close();
				}
			} catch (SQLException e) {
			}
		}
		log.info("完成:error=" + error+",总数="+index);
	}

	private static void getInsertSql(Connection connMysql) throws SQLException {
		Statement stmt = connMysql.createStatement();
		String querySql = "select * from " + MYSQL_TABLE;
		ResultSet rs = stmt.executeQuery(querySql);
		ResultSetMetaData mt = rs.getMetaData();
		int cols = mt.getColumnCount();
		StringBuffer sb = new StringBuffer(128);
		sb.append("insert into ");
		sb.append(SqlUtils.MYSQL_DB);
		sb.append(".");
		sb.append(MYSQL_TABLE_JYT_BACKUP);
		sb.append("(");
		for (int i = 1; i <= cols; i++) {
			String name = mt.getColumnName(i);
			if (i != 1) {
				sb.append(",");
			}
			if (!name.equalsIgnoreCase("rn")) {
				sb.append(name);
			}
		}
		sb.append(") values (");
		for (int c = 1; c <= cols; c++) {
			if (c != 1) {
				sb.append(",");
			}
			sb.append("?");
		}
		sb.append(")");
		rs.close();
		stmt.close();
		mysqlInsertSql = sb.toString();
		mysqlcolsSql = cols;
	}
}